MySQL 优化常见问题

谈谈你对数据库读写分离的理解？

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

MySQL的查询优化

使用索引（但是也不能随便创建索引）
使用 join 代替子查询
尽量用 IN 代替 OR，OR 的效率是 $n$ 级别，IN 的效率是 $log(n)$ 级别，IN 的个数建议控制在 200 以内
能用 BETWEEN 不用 IN
尽量不要使用 select *，而是具体字段
切分一个连接时间很长的查询，或返回数据量很大的查询
分解关联查询，在应用层做关联，可以更容易对数据库进行拆分，减少锁的竞争，减少冗余记录的查询

二的原因：

因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

三的原因：

对于许多数据库服务器而言，IN() 列表不过是多个 OR 语句的同义词而已，因为 IN 和 OR 在逻辑上是等同的。不仅是在 MySQL 数据库服务器，对于许多其他的数据库服务器使用到 IN 查询时，都是按照如下方式处理的：

对 IN 列表中的数值进行排序。
对于查询的匹配，每次使用二分查找去匹配 IN 列表的数值。

所以对于第 2 步，每次比较的算法复杂度大概为 $O(log n)$ 。相反，对于同样逻辑的 OR 列表，每次都要遍历，所以 OR 相应的算法复杂度为 $O(n)$ ,因此对于遍历非常大的OR列表，会很缓慢！。

五的原因：

字段多时，大表能达到 100 多个字段甚至达 200 多个字段
只取需要的字段，节省资源、减少网络开销
select * 进行查询时，很可能不会用到索引，就会造成全表扫描

高并发怎么处理

高并发大多的瓶颈在后台，在存储 MySQL 的正常的优化方案如下：

1、代码中 SQL 语句优化 2、数据库字段优化，索引优化 3、加缓存，Redis 4、主从，读写分离 5、分区表 6、垂直拆分，解耦模块 7、水平切分

1、方法1 和方法2 是最简单，也是提升效率最快的方式。因为每条语句都命中了索引，是最高效的。但是如果是为了使 sql 达到最优而去建索引，那么索引就泛滥了，对于千万级以上的表来说，维护索引的成本大大增加，反而增加了数据库的内存的开销。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的 INSERT，DELETE，UPDATE 将为此多付出 4，5 次的磁盘 I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

2、数据库字段的优化。例如一个日期类型，被设计为 varchar 类型，不规范的同时，无法对写入数据校验，做索引的效率也有差别

3、缓存适合读多写少，更新频度相对较低的业务场景，否则缓存异议不大，命中率不高。缓存通常来说主要为了提高接口处理速度，降低并发带来的 db 压力以及由此产生的其他问题。

4、分区不是分表，结果还是一张表，只不过把存放的数据文件分成了多个小块。在表数据非常大的情况下，可以解决无法一次载入内存，以及大表数据维护等问题。

5、垂直拆分将表按列拆成多表，常见于将主表的扩展数据独立开，文本数据独立开，降低磁盘io的压力。

MySQL大数据量下的优化

建表时

此处考察如何建立索引字段类型尽量精确，尽量小，能用 int 不要用 bigint（尽量让一个数据页装更多数据）尽量不要用 null，声明 not null，如果是 null 用 0 代替尽量使用 TIMESTAMP 而非 DATETIME 用整型来存 ip 注意反范式和范式的优化

查询时

参考查询优化参考前面的查询在什么情况下不走索引

加缓存 NoSQL

Memcached Redis

大数据量下的分页查询优化

参考资料 MySQL分页查询优化？参考资料 MySQL分页查询越来越慢？是时候该优化了！

不使用 OFFSET 和 LIMIT 进行数据库分页

select * from table_name limit 10 offset 40;

OFFSET（偏移量）和 LIMIT 有什么问题？

为了实现分页，每次收到分页请求时（携带 OFFSET），数据库都需要进行低效的全表扫描。

这是因为查询时 MySQL 并不是跳过 OFFSET 行，而是取 OFFSET+N 行，然后放弃前 OFFSET 行，最后返回 N 行，当 OFFSET 特别大的时候，效率就非常的低下。

什么是全表扫描？全表扫描 (又称顺序扫描) 就是在数据库中进行逐行扫描，顺序读取表中的每一行记录，然后检查各个列是否符合查询条件。这种扫描是已知最慢的，因为需要进行大量的磁盘 I/O，而且从磁盘到内存的传输开销也很大。

这意味着，如果你有 1 亿个用户，OFFSET 是 5 千万，那么它需要获取所有这些记录（包括那么多根本不需要的数据），将它们放入内存，然后获取 LIMIT 指定的 20 条结果。

替代方案：记录位置，避免使用 OFFSET

改成基于指针的分页

select * from table_name where id > 10 limit 20;

需要在本地保存上一次接收到的主键 (通常是一个 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查询可能都与此类似。

为什么？因为通过显式告知数据库最新行，数据库就确切地知道从哪里开始搜索（基于有效的索引），而不需要考虑目标范围之外的记录。

要使用这种基于游标的分页，需要有一个惟一的序列字段 (或多个)，比如惟一的整数 ID 或时间戳，但在某些特定情况下可能无法满足这个条件。

例如：

首先获取第一页的结果：

select * from t_order limit 10;

假如上边返回的是 id 为1 ~ 10的记录，我们将 10 这个值记住，下一页查询就可以直接从 10 这个值开始。

select * from t_order where id > 10 limit 10;

这样做，无论翻页到多少页，性能都会很好：

select * from t_order limit 10;                     
select * from t_order where id > 10000 limit 10;    
select * from t_order where id > 100000 limit 10;   
select * from t_order where id > 1000000 limit 10;  
select * from t_order where id > 10000000 limit 10;

而如果我们当前记录的 id 值为 10000，我们想查上一页怎么办呢？返回去查一下即可：

select * from t_order where id <= 10000 order by id desc limit 10,10;

这种优化方式，可以实现上一页、下一页这种的分页。但如果想要实现跳转到指定页码的话，就需要保证 id 连续不中断，再通过计算找到准确的位置。

优化二：计算边界值，转换为已知位置的查询

如果 id 连续不中断，我们就可以计算出每一页的边界值，让 MySQL 根据边界值进行范围扫描，查出数据。

select * from t_order where id between 0 and 10;
select * from t_order where id between 10000 and 10010;
select * from t_order where id between 100000 and 100010;
select * from t_order where id between 1000000 and 1000010;
select * from t_order where id between 10000000 and 10000010;

怎么知道创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因?

使用 Explain 命令来查看语句的执行计划，MySQL 在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引

例如：possilbe_key、key、key_len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。

谈谈你对数据库读写分离的理解？​

MySQL的查询优化​

高并发怎么处理​

MySQL大数据量下的优化​

建表时​

查询时​

加缓存 NoSQL​

大数据量下的分页查询优化​

替代方案：记录位置，避免使用 OFFSET​

优化二：计算边界值，转换为已知位置的查询​

怎么知道创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因?​